<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;
use Phinx\Db\Adapter\MysqlAdapter;

final class ModifyMultipleColumnsInGlobalNoticeTable extends AbstractMigration
{
    public function up()
    {
        $this->table("global_notice")
            ->addColumn('created_by', 'integer', ['signed' => false, 'default' => 0, 'limit' => 11, 'comment' => '创建者'])
            ->addColumn('created', 'integer', ['signed' => false, 'default' => 0, 'limit' => 11, 'comment' => '创建时间'])
            ->save();

        // 更新字段
        $this->execute('update global_notice set created_by = publisher, created = created_at');

        // 重建公告数据
        $this->rebuildGlobalNotice();

        $this->table("global_notice")
            ->removeColumn('content')
            ->removeColumn('publisher')
            ->removeColumn('created_at')
            ->save();
    }

    public function down()
    {
        // 回退字段
        $this->table('global_notice')
            ->addColumn('content', 'text', ['null' => false, 'limit' => MysqlAdapter::TEXT_LONG, 'comment' => '公告内容,支持富文本'])
            ->addColumn('publisher', 'integer', ['signed' => false, 'null' => false, 'default' => 0, 'limit' => 10, 'comment' => '公告发布者'])
            ->addColumn('created_at', 'integer', ['signed' => false, 'null' => false, 'default' => 0, 'limit' => 11, 'comment' => '公告发布时间'])
            ->save();
        $this->execute('update global_notice set publisher = created_by, created_at = created');
        $this->table("global_notice")
            ->removeColumn('created_by')
            ->removeColumn('created')
            ->save();
    }

    // 重建公告数据
    public function rebuildGlobalNotice()
    {
        // 取公告模块id
        $globalNoticeModuleRes = $this->query('select id, code from module where code in ("global_notice", "user")');
        $globalNoticeModuleId = 0;
        $userModuleId = 0;
        foreach ($globalNoticeModuleRes as $item) {
            if ($item['code'] == 'global_notice') {
                $globalNoticeModuleId = $item['id'];
            } else if ($item['code'] == 'user') {
                $userModuleId = $item['id'];
            }
        }
        // 公告内容创建note
        $this->moveNoticeContentIntoNote($globalNoticeModuleId);
        // 公告评论重新组织
        $this->dealNoticeComment($globalNoticeModuleId);
        // 公告确认重新组织
        $this->dealNoticeConfirm($globalNoticeModuleId, $userModuleId);
    }

    // 公告内容创建note
    public function moveNoticeContentIntoNote($globalNoticeModuleId)
    {
        $noticeCommentListRes = $this->query('select link_id, tenant_id from note'
            . ' where module_id = 0'
            . ' and type = "text"'
            . ' and json->>"$.comment_user" is not null');
        $noticeCommentTenantMap = [];
        foreach ($noticeCommentListRes as $comment) {
            $noticeCommentTenantMap[$comment['link_id']] = $comment['tenant_id'];
        }

        $noticeListRes = $this->query('select * from global_notice');
        $addNoteRows = [];
        foreach ($noticeListRes as $notice) {
            $json = json_decode($notice['json'], true);
            $addNoteRows[] = [
                'type' => 'text',
                'module_id' => $globalNoticeModuleId,
                'tenant_id' => $noticeCommentTenantMap[$notice['id']] ?? 0,
                'link_id' => $notice['id'],
                'demand_type' => 'default',
                'text' => $notice['content'],
                'json' => json_encode([
                    'note_cos_media' => $json['global_notice_attachment'] ?? ''
                ]),
                'created' => $notice['created_at'],
                'created_by' => $notice['publisher'],
                'last_updated' => $notice['created_at'],
                'last_updated_by' => $notice['publisher'],
            ];
        }
        $this->table('note')->insert($addNoteRows)->save();
    }

    // 公告评论重新组织
    public function dealNoticeComment($globalNoticeModuleId)
    {
        // 公告评论
        // 1. 补充 module_id
        // 2. 补充 类型 comment
        // 3. 转换 父级评论id
        $this->execute('update note'
            . ' set module_id = ' . $globalNoticeModuleId
            . ', demand_type = "comment"'
            . ', comment_subject_id = if (json->>"$.pid" is not null, cast(json->>"$.pid" as unsigned), 0)'
            . ' where module_id = 0'
            . ' and type = "text"'
            . ' and json->>"$.comment_user" is not null');
    }

    // 公告确认重新组织
    public function dealNoticeConfirm($globalNoticeModuleId, $userModuleId)
    {
        $noticeConfirmListRes = $this->query(
            'select id, tenant_id, created_by, created '
            . ' , link_id as user_id '
            . ' , json->>"$.notice_id" as notice_id '
            . ' , json->>"$.is_confirm" as is_confirm '
            . ' , json->>"$.link_data_type" as link_data_type '
            . ' from note'
            . ' where module_id = ' . $userModuleId
            . ' and type = "text"'
            . ' and json->>"$.notice_id" is not null'
            . ' and json->>"$.is_confirm" is not null'
            . ' and json->>"$.link_data_type" is not null');
        $noticeConfirmMap = [];
        $noticeConfirmNoteDelIds = [];
        foreach ($noticeConfirmListRes as $confirmItem) {
            $noticeConfirmNoteDelIds[] = $confirmItem['id'];
            $noticeConfirmMap[$confirmItem['notice_id']][] = $confirmItem;
        }
        // 按照确认添加
        $noticeListRes = $this->query('select * from global_notice');
        $addConfirmRows = [];
        foreach ($noticeListRes as $notice) {
//            $json = json_decode($notice['json'], true);
            foreach ($noticeConfirmMap[$notice['id']] ?? [] as $confirmItem) {
                // 确认人id、确认状态、确认时间
                $addConfirmRows[] = [
                    'link_id' => $notice['id'],
                    'link_module_code' => 'global_notice',
                    'confirm_user_id' => $confirmItem['user_id'],
                    'confirm' => $confirmItem['is_confirm'] == 1 ? 'yes' : 'default',
                    'source_type' => $confirmItem['link_data_type'] == 1 ? 'global_notice_confirm' : 'global_notice_at',
                    'remark' => '',
                    'tenant_id' => $confirmItem['tenant_id'],
                    'created_by' => $confirmItem['created_by'],
                    'created' => $confirmItem['created'],
                ];
            }
        }
        $this->table('message_task_confirm')->insert($addConfirmRows)->save();
        // 从note中删除原确认条目
        if (!empty($noticeConfirmNoteDelIds)) {
            $noticeConfirmNoteDelRes = $this->query(
                'delete from note'
                . ' where id in ('
                . implode(',', $noticeConfirmNoteDelIds)
                . ')');
        }
    }
}
